# load modules and functions 
import os
import numpy as np
import pandas as pd
from pandas import DataFrame

import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go

from colorama import Fore, Back, Style
from datetime import time, timedelta

# load DataFrames:
calls = pd.read_pickle("01_calls.pkl")
deals = pd.read_pickle("04_deals.pkl")

################## trends calculation
# converting tadatime in date
deals["Deal_Created_Date"] = deals["Created_Time"].dt.date
calls["Call_Start_Date"] = calls["Call_Start_Time"].dt.date

# Grouping calls by Call_Start_Date, counting the number of calls
calls_trend = calls.groupby("Call_Start_Date")["Id"].count().reset_index()
calls_trend.rename(columns={"Id": "Calls_Made"}, inplace=True)

# Grouping deals by Deal_Created_Date, counting the number of created deals
deals_trend = deals.groupby("Deal_Created_Date")["Id"].count().reset_index()
deals_trend.rename(columns={"Id": "Deals_Created"}, inplace=True)

# data merge by data columns, using Inner Join to select data on the  equal time intervals
trend_data = deals_trend.merge(calls_trend, left_on="Deal_Created_Date", right_on="Call_Start_Date", how="inner")

# display(trend_data)
################# trends visualisation 
fig = go.Figure()
fig.add_trace(go.Scatter(x=trend_data["Deal_Created_Date"], y=trend_data["Deals_Created"], # deals - blue
                         mode='lines', name="Deals Created", line=dict(color='blue')))
fig.add_trace(go.Scatter(x=trend_data["Call_Start_Date"], y=trend_data["Calls_Made"], # calls - green
                         mode='lines', name="Calls Made", line=dict(color='green')))
# min and max for Deals_Created
min_deals_idx = trend_data["Deals_Created"].idxmin()
max_deals_idx = trend_data["Deals_Created"].idxmax()
fig.add_trace(go.Scatter(x=[trend_data["Deal_Created_Date"][min_deals_idx], trend_data["Deal_Created_Date"][max_deals_idx]],
                         y=[trend_data["Deals_Created"][min_deals_idx], trend_data["Deals_Created"][max_deals_idx]],
                         mode='markers', name="Min/Max Deals", marker=dict(color='blue', size=10)))
# min and max for Calls_Made
min_calls_idx = trend_data["Calls_Made"].idxmin()
max_calls_idx = trend_data["Calls_Made"].idxmax()
fig.add_trace(go.Scatter(x=[trend_data["Call_Start_Date"][min_calls_idx], trend_data["Call_Start_Date"][max_calls_idx]],
                         y=[trend_data["Calls_Made"][min_calls_idx], trend_data["Calls_Made"][max_calls_idx]],
                         mode='markers', name="Min/Max Calls", marker=dict(color='green', size=10)))
# visalisation set up
fig.update_layout(title="Dynamics of trade creation and their relationship with calls over time (day interval)",
                  xaxis_title="Date", yaxis_title="Count", legend_title="Metrics")
fig.show()

################## Trends Calculation
# Convert datetime to monthly periods (`to_period('M')`)
deals["Deal_Created_Date"] = deals["Created_Time"].dt.to_period('M')
calls["Call_Start_Date"] = calls["Call_Start_Time"].dt.to_period('M')

# Group calls by month, counting the number of calls
calls_trend_m = calls.groupby("Call_Start_Date")["Id"].count().reset_index()
calls_trend_m.rename(columns={"Id": "Calls_Made"}, inplace=True)

# Group deals by month, counting the number of created deals
deals_trend_m = deals.groupby("Deal_Created_Date")["Id"].count().reset_index()
deals_trend_m.rename(columns={"Id": "Deals_Created"}, inplace=True)

# Merge deals_trend_m and calls_trend_m based on the month, using Inner Join to select data on the  equal time intervals
trend_data_m = deals_trend_m.merge(calls_trend_m, left_on="Deal_Created_Date", right_on="Call_Start_Date", how="inner")

################## Trends Visualization
# Convert monthly periods back to datetime for proper visualization in matplotlib
trend_data_m["Deal_Created_Date"] = trend_data_m["Deal_Created_Date"].dt.to_timestamp()
trend_data_m["Call_Start_Date"] = trend_data_m["Call_Start_Date"].dt.to_timestamp()

# Plot the trends
plt.figure(figsize=(12, 6))
# Plot deals trend (blue line)
plt.plot(trend_data_m["Deal_Created_Date"], trend_data_m["Deals_Created"], marker="o", linestyle="-", color="blue", label="Deals Created")
# Plot calls trend (green line)
plt.plot(trend_data_m["Call_Start_Date"], trend_data_m["Calls_Made"], marker="s", linestyle="-", color="green", label="Calls Made")
# Add data labels to each point
for i in range(len(trend_data_m)):
    plt.annotate(trend_data_m["Deals_Created"].iloc[i], (trend_data_m["Deal_Created_Date"].iloc[i], trend_data_m["Deals_Created"].iloc[i]), 
                 textcoords="offset points", xytext=(0,5), ha='center', fontsize=10, color='blue')
    plt.annotate(trend_data_m["Calls_Made"].iloc[i], (trend_data_m["Call_Start_Date"].iloc[i], trend_data_m["Calls_Made"].iloc[i]), 
                 textcoords="offset points", xytext=(0,5), ha='center', fontsize=10, color='green')
# Improve visualization
plt.xlabel("Month")
plt.ylabel("Count")
plt.title("Monthly Trend of Deals Created and Calls Made")
plt.xticks(rotation=45)
plt.legend()
plt.grid(True)
plt.show()

import statsmodels.api as sm

# Copy the dataframe before changing the index
df = trend_data.copy()

# Set date as index
df.set_index("Deal_Created_Date", inplace=True)

# Check for missing values before analysis
df["Deals_Created"] = df["Deals_Created"].fillna(0)

# time series decomposition (30-day seasonality)
decomposition = sm.tsa.seasonal_decompose(df["Deals_Created"], model="additive", period=30)

# visualisation
fig, axes = plt.subplots(3, 1, figsize=(10, 8), sharex=True)
decomposition.trend.plot(ax=axes[0], title="Trend", color="blue")
decomposition.seasonal.plot(ax=axes[1], title="Seasonality", color="orange")
decomposition.resid.plot(ax=axes[2], title="Noise", color="black")
axes[0].set_ylabel("Trend")
axes[1].set_ylabel("Seasonality")
axes[2].set_ylabel("Noise")
plt.xlabel("Date")
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Average amplitude of seasonal fluctuations and noise variance
print("Average amplitude of seasonality:", round(decomposition.seasonal.std(), 2))
print("Average noise level:", round(decomposition.resid.std(), 2))

Average amplitude of seasonality: 7.99
Average noise level: 24.34

pd.options.display.float_format = '{:.2f}'.format
# correlation between the number of calls and the number of deals
correlation = trend_data[["Deals_Created", "Calls_Made"]].corr()
print(Back.YELLOW + "\nCorrelation between the number of calls and the number of deals:\n"+ Style.RESET_ALL)
display(correlation)

# Build a correlation heat map
plt.figure(figsize=(6, 4))
sns.heatmap(correlation, annot=True, cmap="YlGnBu", fmt=".2f")
plt.title("Тепловая карта корреляции", fontsize=14)
plt.show()


Correlation between the number of calls and the number of deals:

# determine the final stages of deals
final_stages = ['Qualificated',"Payment Done", "Waiting For Payment",'Lost']

# filtering deals that have reached the final stage
deals_final = deals[deals['Stage'].isin(final_stages)]

# Соединяем звонки со сделками по CONTACTID -- Contact_Name
calls_deals = calls.merge(deals_final[['Id', 'Stage', 'Created_Time', 'Contact_Name']], 
                          left_on='CONTACTID', 
                          right_on='Contact_Name', 
                          how='inner'
                         ).dropna()

# Вычисляем разницу между звонком и финальной стадией сделки
calls_deals["Time_to_Final_Stage"] = abs((calls_deals["Created_Time"] - calls_deals["Call_Start_Time"]).dt.days)

# Рассчитываем среднее время для каждой финальной стадии
time_analysis = calls_deals.groupby("Stage", observed=False)["Time_to_Final_Stage"].mean().reset_index()
time_analysis.rename(columns={"Time_to_Final_Stage": "Avg_Days_To_Final_Stage"}, inplace=True)

# Вывод результата
display(time_analysis.dropna().sort_values(by='Avg_Days_To_Final_Stage').round(0))
time_analysis.dropna().sort_values(by='Avg_Days_To_Final_Stage').round(0).plot(kind='bar',
                                                                               x='Stage',
                                                                               y='Avg_Days_To_Final_Stage',
                                                                               title='Deals Moving Speed to final Stages after Calls (in days)')
plt.show()

# Количество звонков по этапам сделок

# Группировка звонков по CONTACTID перед объединением
calls_agg = calls.groupby("CONTACTID").agg({"Id": "count", "Call_Duration": "mean"}).reset_index()
calls_agg.rename(columns={"Id": "Total_Calls"}, inplace=True)

# Объединение агрегированных звонков со сделками
calls_agg_deals = calls_agg.merge(deals, left_on="CONTACTID", right_on="Contact_Name", how="inner").drop_duplicates(subset="CONTACTID")

# Группировка по этапу сделки с суммированием числа звонков
calls_stage = calls_agg_deals.groupby(["Stage"],observed=True).agg({"Total_Calls": "sum"}).reset_index()

print(Back.YELLOW +"Количество звонков по этапам сделок:"+ Style.RESET_ALL)
calls_stage = calls_stage.sort_values(by="Total_Calls", ascending=False)
print(calls_stage)
calls_stage.plot(kind='bar',
                 x="Stage",
                 y="Total_Calls",
                 title="Calls Number  by Deal Stages",
                 legend=False,
                 grid=True,
                 alpha=0.5,
                 fontsize=9)

Количество звонков по этапам сделок:
                        Stage  Total_Calls
2                        Lost        66337
0                Call Delayed         9044
7                Payment Done         8455
10      Registered on Webinar         7378
12        Waiting For Payment         2285
9   Registered on Offline Day          783
8                Qualificated          610
5        Need to Call - Sales          218
11                  Test Sent          178
3                Need To Call          115
4         Need a consultation           98
1              Free Education            8
6                    New Lead            1

<Axes: title={'center': 'Calls Number  by Deal Stages'}, xlabel='Stage'>

# Анализ средней продолжительности з# Анализ средней продолжительности звонков по этапам сделок
avg_duration = calls_agg_deals.groupby("Stage", observed=True)["Call_Duration"].mean().reset_index()

# Сортировка по среднему времени звонков (Call_Duration)
avg_duration = avg_duration.sort_values(by="Call_Duration", ascending=False)

print(Back.YELLOW + "Средняя продолжительность звонков на разных этапах сделки:" + Style.RESET_ALL)
print(avg_duration)
avg_duration.plot(kind='bar',
                  x="Stage", 
                  y="Call_Duration", 
                  title="Average Duration of Calls at different Deal Stages",
                  legend=False,
                  grid=True,
                  alpha=0.5,
                  fontsize=9)

Средняя продолжительность звонков на разных этапах сделки:
                        Stage  Call_Duration
11                  Test Sent         679.33
8                Qualificated         444.08
12        Waiting For Payment         369.23
7                Payment Done         298.06
4         Need a consultation         295.30
0                Call Delayed         291.09
9   Registered on Offline Day         258.95
10      Registered on Webinar         249.92
1              Free Education         228.88
2                        Lost         173.62
5        Need to Call - Sales          83.54
6                    New Lead          30.00
3                Need To Call          13.77

<Axes: title={'center': 'Average Duration of Calls at different Deal Stages'}, xlabel='Stage'>

# Convert closing dates to monthly periods
deals["Closing_Month"] = deals["Closing_Date"].dt.to_period("M")

# Group deals by closing month
closing_distribution = deals.groupby("Closing_Month")["Id"].count().reset_index()

# Seaborn bar plot for deal closures per month
plt.figure(figsize=(12, 6))
ax = sns.barplot(x=closing_distribution["Closing_Month"].astype(str), 
                 y=closing_distribution["Id"], 
                 color="lightblue")  # Using a single color instead of `palette`

# Add data labels formatted as integers (no decimal places)
for p in ax.patches:
    ax.annotate(f"{int(p.get_height())}", 
                (p.get_x() + p.get_width() / 2, p.get_height()), 
                ha="center", va="bottom", fontsize=10, color="black")

# Customize plot appearance
plt.xticks(rotation=45)
plt.xlabel("Month of Deal Closure")
plt.ylabel("Number of Closed Deals")
plt.title("Distribution of Closed Deals per Month")
plt.show()

# Calculate deal duration (days between creation and closure)
deals["Closing_Duration"] = (deals["Closing_Date"] - deals["Created_Time"]).dt.days

# Seaborn histogram for deal duration
plt.figure(figsize=(10, 5))
ax = sns.histplot(deals["Closing_Duration"].dropna(), bins=30, kde=True, color="blue", edgecolor="black")

# Add data labels on histogram bins
for p in ax.patches:
    height = p.get_height()
    if height > 0:  # Only annotate non-zero bins
        ax.annotate(f"{int(height)}", (p.get_x() + p.get_width() / 2, height), 
                    ha="center", va="bottom", fontsize=9, color="black")

# Customize plot appearance
plt.xlabel("Days Until Deal Closure")
plt.ylabel("Number of Deals")
plt.title("Deals Distribution of Creation to Closure time")
plt.show()

Этап	Среднее время (дней)
Qualificated	27
Waiting For Payment	34
Lost	37
Payment Done	40

3. Анализ временных рядов¶

Загрузка модулей и функций¶

3.1 Анализ тенденций создания сделок с течением времени и их связь со звонками¶

Динамика создания сделок и их связь со звонками во времени¶

Тенденция заключенных сделок и совершенных звонков по месяцам¶

Декомпозиция временного ряда¶

Корреляция между числом звонков и числом новых сделок¶

Анализ времени перехода сделок на финальные этапы после звонков¶

Анализ количества звонков по этапам сделок¶

Анализ средней продолжительности звонков по этапам сделок¶

3.2 Pаспределение времени закрытия сделок и продолжительность периода от создания до закрытия.¶

Распределение времени закрытия сделок¶

Анализ продолжительности периода от создания до закрытия сделки¶

3.3 Общие выводу по проведенному анализу временных рядов сделок и звонков¶

Динамика создания сделок и их связь со звонками¶

Корреляция звонков и сделок¶

Время перехода сделок на финальные этапы¶

Анализ звонков по этапам сделок¶

Распределение закрытых сделок по месяцам¶

Распределение длительности сделок (от создания до закрытия)¶

Выводы и рекомендации¶

	Stage	Avg_Days_To_Final_Stage
8	Qualificated	23.00
2	Lost	37.00
12	Waiting For Payment	44.00
7	Payment Done	45.00

	Deals_Created	Calls_Made
Deals_Created	1.00	0.57
Calls_Made	0.57	1.00